/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workdb;
import java.sql.*;
import java.util.Locale;
import oracle.jdbc.pool.OracleDataSource;
/**
 *
 * @author Андрей
 */
public class database {
    static Connection setConnection() throws SQLException{
        Locale.setDefault(Locale.ENGLISH);
        OracleDataSource ds = new OracleDataSource ( );
        ds.setDriverType("thin");
        ds.setServerName("localhost");
        ds.setPortNumber(1521);
        ds.setDatabaseName("xe");
        ds.setUser("cracker");
        ds.setPassword("cracker123");
        Connection conn = ds.getConnection( );
        return conn;
    }
    public static void delEmp(String id) throws SQLException{
        Connection conn=database.setConnection();
        PreparedStatement stat=conn.prepareStatement("delete from emp where empno=?");
        stat.setString(1, id);
        stat.executeUpdate();
        conn.commit();
        stat.close();
        conn.close();
    }
    public static String[] findEmp(String id) throws SQLException{
        Connection conn=database.setConnection();
        PreparedStatement stat=conn.prepareStatement("select * from emp where empno=?");
        stat.setString(1, id);
        ResultSet rs=stat.executeQuery();
        String [] res=new String[7];
        while(rs.next()){
          res[0]=rs.getString(2);
          res[1]=rs.getString(3);
          res[2]=rs.getString(4);
          res[3]=rs.getString(5);
          String s="",s2="";
          for(int i=2;i<10;i++){
              s+=res[3].charAt(i);
          }
          s2+=s.charAt(s.length()-2);
          s2+=s.charAt(s.length()-1);
          s2+="-";
          s2+=s.charAt(s.length()-5);
          s2+=s.charAt(s.length()-4);
          s2+="-";
          s2+=s.charAt(s.length()-8);
          s2+=s.charAt(s.length()-7);
          res[3]=s2;
          res[4]=rs.getString(6);
          res[5]=rs.getString(7);
          res[6]=rs.getString(8);
        }
        conn.commit();
        stat.close();
        conn.close();
        return res;
    }
    public static void newEmp(String id,String ename,String job,String mgr,String hiredate,String sal,String comm,String deptno) throws SQLException{
        mgr=mgr.trim();
        comm=comm.trim();
        if(mgr.equals("")){
            mgr="null";
        }
        if(comm.equals("")){
            comm="null";
        }
        Connection conn=database.setConnection();
        /*PreparedStatement stat=conn.prepareStatement("INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)"
                                                    +"VALUES (?, '?', '?', ?, to_date('?', 'dd-mm-yy'), ?, ?, ?)");
        stat.setString(1, id);
        stat.setString(2, ename);
        stat.setString(3, job);
        stat.setString(4, mgr);
        stat.setString(5, hiredate);
        stat.setString(6, sal);
        stat.setString(7, comm);
        stat.setString(8, deptno);
        stat.executeUpdate();*/
        Statement stat = conn.createStatement();
        stat.executeUpdate("INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)"
                                    +"VALUES ("+id+", '"+ename+"', '"+job+"', "+mgr+", to_date('"+hiredate+"', 'dd-mm-yy'), "+sal+", "+comm+", "+deptno+")");
        conn.commit();
        stat.close();
        conn.close();
    }
    public static void editEmp(String id,String ename,String job,String mgr,String hiredate,String sal,String comm,String deptno) throws SQLException{
        mgr=mgr.trim();
        comm=comm.trim();
        if(mgr.equals("")){
            mgr="null";
        }
        if(comm.equals("")){
            comm="null";
        }
        Connection conn=database.setConnection();
        Statement stat = conn.createStatement();
        stat.executeUpdate("UPDATE emp SET ename='"+ename+"', job='"+job+"', mgr="+mgr+", hiredate=to_date('"+hiredate+"', 'dd-mm-yy'), sal="+sal+", comm="+comm+", deptno="+deptno+" Where empno="+id);
        conn.commit();
        stat.close();
        conn.close(); 
    }
}
